@echo off
chcp 65001 >nul
setlocal EnableDelayedExpansion

REM 数据库备份脚本 (Windows)
REM Database Backup Script for Delivery Management System

set "DB_PATH=..\data\delivery-management.db"
set "BACKUP_DIR=.\backup"
set "SCHEMA_DIR=.\schema"
set "DATA_DIR=.\data"

echo ================================================
echo    交付管理系统数据库备份脚本
echo    Delivery Management System Database Backup Script
echo ================================================
echo.

REM 检查数据库文件是否存在
if not exist "%DB_PATH%" (
    echo [错误] 数据库文件 %DB_PATH% 不存在
    echo [Error] Database file %DB_PATH% does not exist
    pause
    exit /b 1
)

REM 检查sqlite3是否可用
sqlite3 -version >nul 2>&1
if errorlevel 1 (
    echo [错误] sqlite3 未找到。请确保 sqlite3.exe 在系统 PATH 中。
    echo [Error] sqlite3 not found. Please ensure sqlite3.exe is in system PATH.
    echo.
    echo 您可以从以下地址下载 sqlite3:
    echo You can download sqlite3 from:
    echo https://www.sqlite.org/download.html
    pause
    exit /b 1
)

REM 创建目录
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"
if not exist "%SCHEMA_DIR%" mkdir "%SCHEMA_DIR%"
if not exist "%DATA_DIR%" mkdir "%DATA_DIR%"

echo [信息] 开始备份数据库...
echo [Info] Starting database backup...
echo.

REM 1. 导出完整备份
echo 导出完整数据库备份...
echo Exporting full database backup...
sqlite3 "%DB_PATH%" ".dump" > "%BACKUP_DIR%\full-backup.sql"

REM 2. 导出数据库结构
echo 导出数据库结构...
echo Exporting database schema...
sqlite3 "%DB_PATH%" ".schema" > "%SCHEMA_DIR%\schema.sql"

REM 3. 导出核心表数据
echo 导出用户和角色数据...
echo Exporting users and roles data...

sqlite3 "%DB_PATH%" "SELECT 'INSERT INTO roles VALUES(' || quote(id) || ',' || quote(code) || ',' || quote(name) || ',' || quote(description) || ',' || quote(permissions) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM roles;" > "%DATA_DIR%\roles.sql"

sqlite3 "%DB_PATH%" "SELECT 'INSERT INTO users VALUES(' || quote(id) || ',' || quote(username) || ',' || quote(password) || ',' || quote(email) || ',' || quote(full_name) || ',' || quote(role_id) || ',' || quote(is_active) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM users;" > "%DATA_DIR%\users.sql"

echo 导出设备数据...
echo Exporting devices data...
sqlite3 "%DB_PATH%" "SELECT 'INSERT INTO devices VALUES(' || quote(id) || ',' || quote(device_id) || ',' || quote(name) || ',' || quote(specialty) || ',' || quote(classification_name) || ',' || quote(classification_code) || ',' || quote(naming_table_id) || ',' || quote(position_table_id) || ',' || quote(base_params_table_id) || ',' || quote(tech_spec_table_id) || ',' || quote(sequence_number) || ',' || quote(remarks) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM devices;" > "%DATA_DIR%\devices.sql"

echo 导出参数元数据...
echo Exporting parameter metadata...
sqlite3 "%DB_PATH%" "SELECT 'INSERT INTO parameter_field_metadata VALUES(' || quote(id) || ',' || quote(table_name) || ',' || quote(table_code) || ',' || quote(table_description) || ',' || quote(field_sequence) || ',' || quote(field_name) || ',' || quote(field_column) || ',' || quote(example_description) || ',' || quote(data_type) || ',' || quote(measurement_class) || ',' || quote(data_source) || ',' || quote(is_required) || ',' || quote(created_at) || ',' || quote(updated_at) || ');' FROM parameter_field_metadata;" > "%DATA_DIR%\parameter_field_metadata.sql"

sqlite3 "%DB_PATH%" "SELECT 'INSERT INTO parameter_table_metadata VALUES(' || quote(id) || ',' || quote(table_name) || ',' || quote(table_code) || ',' || quote(description) || ',' || quote(source_file) || ',' || quote(created_at) || ');' FROM parameter_table_metadata;" > "%DATA_DIR%\parameter_table_metadata.sql"

echo.
echo [成功] 备份完成！
echo [Success] Backup completed!
echo.

REM 显示备份统计信息
echo ================================================
echo 备份统计信息 / Backup Statistics:
echo ================================================

REM 显示文件大小
for %%F in ("%BACKUP_DIR%\full-backup.sql") do set size=%%~zF
echo 完整备份文件 / Full backup file: !size! bytes

for %%F in ("%SCHEMA_DIR%\schema.sql") do set size=%%~zF
echo 结构文件 / Schema file: !size! bytes

echo.
echo 数据文件 / Data files:
for %%F in ("%DATA_DIR%\*.sql") do (
    set size=%%~zF
    echo   %%~nxF: !size! bytes
)

echo.
echo [成功] 所有备份文件已生成完成！
echo [Success] All backup files have been generated!
echo.
pause